﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace SQLServerDAL
{
    public class VideoDAL : IDAL.IVideoDAL
    {
        // 得到指定类型的所有试看影片
        public List<Video> GetVideosByTypeIdAndIsTry(int typeId)
        {
            List<Video> arrList = new List<Video>();
            SQLServerDAL dal = new SQLServerDAL();
            dal.CommandText = "SELECT * FROM [iMovie].[dbo].[VideoInfo_Tab] where [video_isTry]= 'True' and [video_type]= " + typeId;
            System.Diagnostics.Debug.WriteLine(dal.CommandText);
            dal.ExecuteReader();
            while (dal.Reader.Read())
            {
                Video tempVideo = new Video();
                FillVideoWithReader(tempVideo, dal.Reader);
                arrList.Add(tempVideo);
            }
            Console.WriteLine("arrList " + arrList.Count);

            return arrList;

        }

        //获得所有的试看影片
        public List<Model.Video> GetVideosIsTry()
        {
            List<Video> arrList = new List<Video>();
            SQLServerDAL dal = new SQLServerDAL();
            dal.CommandText = "SELECT * FROM [iMovie].[dbo].[VideoInfo_Tab] where [video_isTry]= 'True'";
            dal.ExecuteReader();
            while (dal.Reader.Read())
            {
                Video tempVideo = new Video();
                FillVideoWithReader(tempVideo, dal.Reader);
                arrList.Add(tempVideo);
            }
            Console.WriteLine("arrList " + arrList.Count);

            return arrList;
        }
        // 删除影片
        public bool DeleteVideoById(int id)
        {
            SQLServerDAL dal = new SQLServerDAL();
            Model.Video video = GetVideoById(id);
            File.Delete(HttpContext.Current.Server.MapPath("~") + "movies\\" + video.VideoURL);
            File.Delete(HttpContext.Current.Server.MapPath("~") + "frontStage\\" + video.VideoImage.Replace("/", "\\"));
            dal.CommandText = "DELETE FROM VideoInfo_Tab WHERE video_Id = " + id;
            dal.ExecuteNonQuery();
            return true;
        }

        // 添加新的影片
        public bool AddVideo(Video video)
        {
            SQLServerDAL dal = new SQLServerDAL();

            dal.CommandText = "INSERT INTO VideoInfo_Tab(video_name, video_fileType, video_director, video_actors, video_language, video_maker, video_nation, video_createdDate, video_summary, video_image, video_hotScore, video_type, video_url, video_uploadDate, video_isTry, video_typeName) VALUES(@video_name, @video_fileType, @video_director, @video_actors, @video_language, @video_maker, @video_nation, @video_createdDate, @video_summary, @video_image, @video_hotScore, @video_type, @video_url, @video_uploadDate, @video_isTry, @video_typeName)";
            dal.AddParameter("@video_name", video.VideoName);
            dal.AddParameter("@video_type", video.VideoType);
            dal.AddParameter("@video_createdDate", video.VideoCreatedDate);
            dal.AddParameter("@video_maker", video.VideoMaker);
            dal.AddParameter("@video_hotScore", video.VideohotScore);
            dal.AddParameter("@video_summary", video.VideoSummary);
            dal.AddParameter("@video_director", video.VideoDirector);
            dal.AddParameter("@video_actors", video.VideoActors);
            dal.AddParameter("@video_language", video.VideoLanguage);
            dal.AddParameter("@video_nation", video.VideoNation);
            dal.AddParameter("@video_url", video.VideoURL);
            dal.AddParameter("@video_uploadDate", video.VideoUploadDate);
            dal.AddParameter("@video_fileType", video.VideoFileType);
            dal.AddParameter("@video_image", video.VideoImage);
            dal.AddParameter("@video_typeName", video.VideoTypeName);
            dal.AddParameter("@video_isTry", video.IsTry);
            dal.ExecuteNonQuery();
            return true;
        }

        // 更新影片信息
        public bool UpdateVideo(Video video)
        {
            SQLServerDAL dal = new SQLServerDAL();

            dal.CommandText = "UPDATE VideoInfo_Tab SET video_name = @video_name, video_fileType = @video_fileType, video_director = @video_director, video_actors = @video_actors, video_language = @video_language, video_maker = @video_maker, video_nation = @video_nation, video_createdDate = @video_createdDate, video_summary = @video_summary, video_image = @video_image, video_hotScore = @video_hotScore, video_type = @video_type, video_url = @video_url, video_uploadDate = @video_uploadDate, video_isTry = @video_isTry, video_typeName = @video_typeName WHERE video_id = @video_id";
            dal.AddParameter("@video_id", video.VideoId);
            dal.AddParameter("@video_name", video.VideoName);
            dal.AddParameter("@video_type", video.VideoType);
            dal.AddParameter("@video_createdDate", video.VideoCreatedDate);
            dal.AddParameter("@video_maker", video.VideoMaker);
            dal.AddParameter("@video_image", video.VideoImage);
            dal.AddParameter("@video_hotScore", video.VideohotScore);
            dal.AddParameter("@video_summary", video.VideoSummary);
            dal.AddParameter("@video_director", video.VideoDirector);
            dal.AddParameter("@video_actors", video.VideoActors);
            dal.AddParameter("@video_language", video.VideoLanguage);
            dal.AddParameter("@video_nation", video.VideoNation);
            dal.AddParameter("@video_url", video.VideoURL);
            dal.AddParameter("@video_uploadDate", video.VideoUploadDate);
            dal.AddParameter("@video_fileType", video.VideoFileType);
            dal.AddParameter("@video_typeName", video.VideoTypeName);
            dal.AddParameter("@video_isTry", video.IsTry);
            dal.ExecuteNonQuery();
            return true;
        }

        // 按指定类型关键字搜索影片
        public List<Video> SearchVideos(string keyType, string keyWord)
        {
            List<Video> arrList = new List<Video>();
            SQLServerDAL dal = new SQLServerDAL();

            string fieldName = keyType;
            //string fieldName = ConfigurationManager.AppSettings[keyType];

            dal.CommandText = "SELECT * FROM VideoInfo_Tab WHERE " + fieldName + " LIKE '%" + keyWord + "%'";
            dal.ExecuteReader();
            while (dal.Reader.Read())
            {
                Video tempVideo = new Video();
                FillVideoWithReader(tempVideo, dal.Reader);
                arrList.Add(tempVideo);
            }

            return arrList;
        }

        // 按指定名称得到影片类型ID
        public int GetVideoTypeIdByName(String name)
        {
            int id = -1;
            SQLServerDAL dal = new SQLServerDAL();
            dal.CommandText = "SELECT videoType_Id FROM VideoInfo_Tab WHERE videoType_name =" + name;
            dal.ExecuteReader();
            if (dal.Reader.Read())
            {
                dal.Reader.GetInt32(0);
            }
            else
            {
                throw new Exception("No video type Id with the given name is found");
            }

            return id;

        }

        // 得到指定类型的所有影片
        public List<Video> GetVideosByTypeId(int typeId)
        {
            List<Video> arrList = new List<Video>();
            int id = -1;
            SQLServerDAL dal = new SQLServerDAL();
            dal.CommandText = "SELECT * FROM VideoInfo_Tab WHERE video_type =" + typeId;
            dal.ExecuteReader();
            while (dal.Reader.Read())
            {
                Video tempVideo = new Video();
                FillVideoWithReader(tempVideo, dal.Reader);
                arrList.Add(tempVideo);
            }

            return arrList;

        }

        // 按指定ID查找指定影片
        public Video GetVideoById(int Id)
        {
            SQLServerDAL dal = new SQLServerDAL();
            dal.CommandText = "SELECT * FROM VideoInfo_Tab WHERE video_id =" + Id;
            dal.ExecuteReader();
            Video tempVideo = null;
            if (dal.Reader.Read())
            {
                tempVideo = new Video();
                FillVideoWithReader(tempVideo, dal.Reader);
            }
            else
            {
                throw new Exception("No video with the given Id is found");
            }

            return tempVideo;
        }

        // 得到所有影片类型名称及其类型ID
        public Dictionary<String, int> GetAllTypeInfo()
        {
            Dictionary<String, int> dic = new Dictionary<String, int>();
            SQLServerDAL dal = new SQLServerDAL();
            dal.CommandText = "SELECT * FROM VideoType_Dic";
            dal.ExecuteReader();
            while (dal.Reader.Read())
            {
                dic.Add(dal.Reader.GetString(1), dal.Reader.GetInt32(0));
            }

            return dic;
        }

        public void FillVideoWithReader(Video video, SqlDataReader reader)
        {
            video.VideoId = Int32.Parse(reader["video_id"].ToString());
            video.VideoName = reader["video_name"].ToString();
            video.VideoFileType = reader["video_fileType"].ToString();
            video.VideoDirector = reader["video_director"].ToString();
            video.VideoActors = reader["video_actors"].ToString();
            video.VideoLanguage = reader["video_language"].ToString();
            video.VideoMaker = reader["video_maker"].ToString();
            video.VideoNation = reader["video_nation"].ToString();
            video.VideoCreatedDate = reader["video_createdDate"].ToString();
            video.VideoSummary = reader["video_summary"].ToString();
            video.VideoImage = reader["video_image"].ToString();
            video.VideohotScore = Convert.ToInt32(reader["video_hotScore"]);
            video.VideoTypeName = reader["video_typeName"].ToString();
            video.VideoURL = reader["video_url"].ToString();
            video.VideoUploadDate = reader["video_uploadDate"].ToString();
            video.IsTry = Boolean.Parse(reader["video_isTry"].ToString());
            video.VideoType = Int32.Parse(reader["video_type"].ToString());
        }
    }
}

